package com.hc.bankonline.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.hc.bankonline.util.BaseDao;

public class BankOnlineInsertDao {
	BaseDao baseDao = new BaseDao();

	public int insertRecordIn(double money, String cardno) {

		String sql = "INSERT into transaction_record(cardno,transaction_date,expense,income,balance,transaction_type,remark) VALUES(?,SYSDATE(),0,?,(SELECT balance FROM account WHERE cardno =?),'存款','宜春支行');";

		Connection connection = baseDao.getConnection();
		PreparedStatement ps = null;

		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, cardno);
			ps.setDouble(2, money);
			ps.setString(3, cardno);

			return ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(connection, ps, null);
		}
		return 0;
	}

	public int insertRecordOut(double money, String cardno) {

		String sql = "INSERT into transaction_record(cardno,transaction_date,expense,income,balance,transaction_type,remark) VALUES(?,SYSDATE(),0,?,(SELECT balance FROM account WHERE cardno =?),'取款','宜春支行');";

		Connection connection = baseDao.getConnection();
		PreparedStatement ps = null;

		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, cardno);
			ps.setDouble(2, money);
			ps.setString(3, cardno);

			return ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			baseDao.closeAll(connection, ps, null);
		}
		return 0;
	}

	public List<Map<String, Object>> searchAll(Date beforetime, Date aftertime,
			String cardno, int indexPage, int pageSize) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		String sql = "SELECT cardno,transaction_date,expense,income,balance,transaction_type,remark from transaction_record WHERE cardno = ? AND transaction_date BETWEEN ? AND ? LIMIT ?,? ;";
		Connection connection = baseDao.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		java.sql.Date date1 = new java.sql.Date(beforetime.getTime());
		java.sql.Date date2 = new java.sql.Date(aftertime.getTime());

		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, cardno);
			ps.setDate(2, date1);
			ps.setDate(3, date2);
			ps.setInt(4, ((indexPage - 1) * pageSize));
			ps.setInt(5, pageSize);

			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
			
				map.put("cardno", rs.getString("cardno"));
				map.put("transactionDate", rs.getDate("transaction_date"));
				map.put("expense", rs.getDouble("expense"));
				map.put("income", rs.getDouble("income"));
				map.put("balance", rs.getDouble("balance"));
				map.put("transactionType", rs.getString("transaction_type"));
				map.put("remark", rs.getString("remark"));

				list.add(map);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;

	}
	
	public int findTotalTable(String cardno){
		
		String sql = "SELECT count(cardno) as count FROM transaction_record WHERE cardno = ?;";
		
		Connection connection = baseDao.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, cardno);
			
			rs = ps.executeQuery();
			while(rs.next()){
				return rs.getInt("count");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
}
